﻿using System;
using System.Collections.Generic;
using SurveySystem.Model;
using System.Data.SqlClient;
using SurveySystem.Utility;

namespace SurveySystem.Repository.MySQL
{
    public class MssqlResponseRepository : ISurveyResponseRepository
    {
        public Response GetResponseByID(int responseID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            Response r = null;

            sql = "SELECT `response_id`, `survey_id`, `participant_id`, `response_time`, `machine` \n" + //
                  "FROM `survey_response` WHERE response_id=@id";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", responseID);

                conn.Open();
                dr = cmd.ExecuteReader();

                if (dr.Read())
                {
                    r = new Response();

                    r.ResponseID = dr.GetInt32(0);
                    r.SurveyID = dr.GetInt32(1);
                    r.ParticipantID = dr.GetString(2);
                    r.ResponseTime = dr.GetDateTime(3);
                    r.Machine = dr.GetString(4);

                }
                else
                {
                    r = null;
                }

                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return r;
        }

        public List<Response> GetResponsesForSurvey(int surveyID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            List<Response> list = new List<Response>();

            sql = "SELECT `response_id`, `survey_id`, `participant_id`, `response_time`, `machine` \n" + //
                  "FROM `survey_response` WHERE survey_id=@id";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", surveyID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    Response r = new Response();

                    r.ResponseID = dr.GetInt32(0);
                    r.SurveyID = dr.GetInt32(1);
                    r.ParticipantID = dr.GetString(2);
                    r.ResponseTime = dr.GetDateTime(3);
                    r.Machine = dr.GetString(4);

                    list.Add(r);
                }


                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public void AddResponse(Response r)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            sql = "INSERT INTO  `survey_response` \n" + //
                  " `survey_id`, `participant_id`, `response_time`, `machine` \n" + //
                  "VALUES \n" + //
                  "@survey_id, @participant_id, @response_time, @machine)";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@survey_id", r.SurveyID);
                cmd.Parameters.AddWithValue("@participant_id", r.ParticipantID);
                cmd.Parameters.AddWithValue("@response_time", r.ResponseTime);
                cmd.Parameters.AddWithValue("@machine", r.Machine);

                // add the new record to the database
                conn.Open();
                cmd.ExecuteNonQuery();


                //retrieve the new ID field from the database
                sql = "SELECT @@IDENTITY";
                cmd = new SqlCommand(sql, conn);
                dr = cmd.ExecuteReader();

                // update the Response object
                if (dr.Read())
                {
                    int newid = dr.GetInt32(0);

                    r.ResponseID = newid;

                }

                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

        }

        public void UpdateResponse(Response r)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;

            sql = "UPDATE `survey_response` \n" + //
                  "   SET `survey_id`      = @survey_id, \n" + //
                  "       `participant_id` = @participant_id, \n" + //
                  "        `response_time` = @response_time, \n" + //
                  "              `machine` = @machine \n" + //
                  " WHERE    `response_id` = @response_id";

            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@survey_id", r.SurveyID);
                cmd.Parameters.AddWithValue("@participant_id", r.ParticipantID);
                cmd.Parameters.AddWithValue("@response_time", r.ResponseTime);
                cmd.Parameters.AddWithValue("@machine", r.Machine);
                cmd.Parameters.AddWithValue("@response_id", r.ResponseID);

                // update the record in the database
                conn.Open();
                cmd.ExecuteNonQuery();


                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }

        public void DeleteResponse(int responseID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;

            sql = "DELETE FROM `survey_response` \n" + //
                  " WHERE `response_id` = @response_id";

            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@response_id", responseID);

                // update the record in the database
                conn.Open();
                cmd.ExecuteNonQuery();


                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }
    }
}
